-- 创建触发器函数，用于自动更新 updated_at 字段
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP(0);  -- 设置 updated_at 为当前时间
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 1. 创建用户表
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_users_username ON users(username);

-- 创建触发器，自动更新 updated_at 字段
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- Uniques structure for table users
ALTER TABLE "public"."users" ADD CONSTRAINT "username" UNIQUE ("username");

-- 2. 创建歌曲表
CREATE TABLE songs (
    song_id BIGSERIAL PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    artist VARCHAR(100) NOT NULL,
    lyricist VARCHAR(100),
    composer VARCHAR(100),
    album VARCHAR(100),
    genre VARCHAR(50),
    lyrics TEXT,
    release_date DATE,
    file_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_songs_title ON songs(title);
CREATE INDEX idx_songs_artist ON songs(artist);
CREATE INDEX idx_songs_created_at ON songs(created_at);
-- CREATE INDEX idx_songs_album ON songs(album);
-- CREATE INDEX idx_songs_genre ON songs(genre);

-- 创建触发器，自动更新 updated_at 字段
CREATE TRIGGER update_songs_updated_at
BEFORE UPDATE ON songs
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

--3. 创建播放列表表
CREATE TABLE playlists (
    playlist_id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_playlists_user_id ON playlists(user_id);
CREATE INDEX idx_playlists_name ON playlists(name);

-- 创建触发器，自动更新 updated_at 字段
CREATE TRIGGER update_playlists_updated_at
BEFORE UPDATE ON playlists
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- 4. 创建播放列表歌曲关联表
CREATE TABLE playlist_songs (
    playlist_song_id BIGSERIAL PRIMARY KEY,
    playlist_id INT NOT NULL,
    song_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_playlist_songs_playlist_id ON playlist_songs(playlist_id);
CREATE INDEX idx_playlist_songs_song_id ON playlist_songs(song_id);


-- 5. 创建用户收藏表
CREATE TABLE user_favorites (
    favorite_id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    song_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_user_favorites_user_id ON user_favorites(user_id);
CREATE INDEX idx_user_favorites_song_id ON user_favorites(song_id);


-- 6. 创建歌曲评论表
CREATE TABLE song_comments (
    comment_id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    song_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_song_comments_user_id ON song_comments(user_id);
CREATE INDEX idx_song_comments_song_id ON song_comments(song_id);

-- 创建触发器，自动更新 updated_at 字段
CREATE TRIGGER update_song_comments_updated_at
BEFORE UPDATE ON song_comments
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- 7. 创建用户角色表, role_name: admin, user, guest, 为了简化这个表是暂时不用，角色信息直接写在用户表里
CREATE TABLE user_roles (
    role_id BIGSERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_user_roles_role_name ON user_roles(role_name);

-- 创建触发器，自动更新 updated_at 字段
CREATE TRIGGER update_user_roles_updated_at
BEFORE UPDATE ON user_roles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- 插入初始化数据
INSERT INTO user_roles (role_id, role_name) VALUES (1, 'admin'), (2, 'user'), (3,'guest');

-- 8. 创建用户与角色关联表
CREATE TABLE user_role_assignment (
    assignment_id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)
);

-- 创建索引
CREATE INDEX idx_user_role_assignment_user_id ON user_role_assignment(user_id);
CREATE INDEX idx_user_role_assignment_role_id ON user_role_assignment(role_id);
